package com.x.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;
import org.springframework.stereotype.Component;

import com.x.model.Shipments;

@Component
public class ShipmentsDAO extends BaseDAO {

	public void upload(List<Shipments> shipments) {
		Session session = this.getSession();
		int i = 0;
		for (Shipments shipment : shipments) {
			session.save(shipment);
			if (i % 1000 == 0) {
				session.flush();
				session.clear();
			}
		}
	}

	public void trancate() {
		String sql = "truncate table shipments";
		this.getSession().createSQLQuery(sql).executeUpdate();
	}

	public void insertConfig() {
		String sql = "insert into configuration (fc,shipMethod,province,city,district,shipments,tt) "
				+ "select s.fc,s.shipMethod,s.province,s.city,s.district,s.shipments,t.tt "
				+ "from (select fc,shipMethod,province,city,district,max(priority) as level "
				+ "from transittime "
				+ "GROUP BY fc,shipMethod,province,city,district"
				+ ") o,shipments s,transittime t "
				+ "where t.fc = o.fc and t.shipMethod = o.shipMethod and t.province = o.province "
				+ "and t.city = o.city and t.district = o.district and t.priority = o.level "
				+ "and ((t.fc<>'all' and t.fc = s.fc)OR (t.fc='all')) "
				+ "AND ((t.shipMethod<>'all' and t.shipMethod = t.shipMethod)OR(t.shipMethod='all')) "
				+ "AND((t.province<>'all' and t.province = s.province)OR(t.province='all')) "
				+ "AND((t.city<>'all' and t.city = s.city)OR(t.city='all')) "
				+ "AND((t.district<>'all' and s.district = t.district)OR(t.district='all')) "
				+ "group by s.fc,s.shipMethod,s.province,s.city,s.district";
		this.getSession().createSQLQuery(sql).executeUpdate();
	}

	public List<Shipments> getShipments(String fc, String shipMethod,
			String province, String city, String district) {
		String sql = "select s.fc,s.shipMethod,s.province,s.city,s.district,s.shipments "
				+ "from shipments s,transittime t "
				+ "where ((t.fc<>'all' and t.fc = s.fc)OR (t.fc='all')) "
				+ "AND ((t.shipMethod<>'all' and t.shipMethod = t.shipMethod)OR(t.shipMethod='all')) "
				+ "AND((t.province<>'all' and t.province = s.province)OR(t.province='all')) "
				+ "AND((t.city<>'all' and t.city = s.city)OR(t.city='all')) "
				+ "AND((t.district<>'all' and s.district = t.district)OR(t.district='all')) "
				+ "and t.fc='"
				+ fc
				+ "' and t.shipMethod='"
				+ shipMethod
				+ "' and t.province='"
				+ province
				+ "' and t.city='"
				+ city
				+ "' and t.district='"
				+ district
				+ "' group by s.fc,s.shipMethod,s.province,s.city,s.district";

		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		if (results == null) {
			return null;
		} else {
			List<Shipments> shipments = new ArrayList<>();
			for (Object[] result : results) {
				Shipments shipment = new Shipments();
				shipment.setFc(result[0].toString());
				shipment.setShipMethod(result[1].toString());
				shipment.setProvince(result[2].toString());
				shipment.setCity(result[3].toString());
				shipment.setDistrict(result[4].toString());
				shipment.setShipments(new BigDecimal(result[5].toString()));
				shipments.add(shipment);
			}
			return shipments;
		}
	}
}
